프로시져 작성시 SELECT INTO 에서 조회된 쿼리의 ROW가 존재하지 않을때 ORA-01403 에러발생 대치

 

1. ROW가 하나만 조회될때
  NVL(MAX(컬럼명), 'NULL일때 대체값 '' 나 0 값') 처리하여 주면 된다.
  Ex)
    SELECT NVL(MAX(DT_WRK), '') INTO V_TEMPA
    FROM TMP_TABLE
    WHERE A = B;

2. select절에 begin ~exception 처리를 하여 해결하는 방법

Ex) begin

                           select   NVL(FIRST_ZERO_APPROVED,'Y')

                           ,from   aaa

                           into   VVV_FIRST_ZERO_APPROVED

                           ,            VVV_ASSESSMENT_STATUS

                           from   gbsc_plan_manage_header

                           where  le              = :parameter.le

                           and    periods_seq     = :SEARCH.PERIODS_SEQ

                           and    EMPLOYEE_NUMBER = :parameter.employee_number;

exception

    when no_data_found then null;

 

ex2)      begin

                           select   NVL(FIRST_ZERO_APPROVED,'Y')

                           into   VVV_FIRST_ZERO_APPROVED

                           ,     VVV_ASSESSMENT_STATUS

                           from   gbsc_plan_manage_header

                           where  le              = :parameter.le

                           and    periods_seq     = :SEARCH.PERIODS_SEQ

                           and    EMPLOYEE_NUMBER = :parameter.employee_number;

             exception

            when no_data_found then null;

                        VVV_FIRST_ZERO_APPROVED := null;

                        VVV_ASSESSMENT_STATUS        := null;

            when too_many_rows then

                        VVV_FIRST_ZERO_APPROVED := null;

                        VVV_ASSESSMENT_STATUS        := null;

             when others then

                        VVV_FIRST_ZERO_APPROVED := null;

                        VVV_ASSESSMENT_STATUS        := null;

             end;